package com.sh.data.engine.domain.shims.dm8.manager;

import com.sh.data.engine.domain.shims.db.DbOptions;
import com.sh.data.engine.domain.shims.db.GenericDbManager;
import com.sh.data.engine.domain.shims.db.model.FieldInfoDomain;
import com.sh.data.engine.domain.shims.db.model.PreviewDataDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain;
import com.sh.data.engine.domain.shims.db.model.TableSizeDomain;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * @author: mengzheng.mhc
 * @date: 2024/6/7 10:06
 */
public class Dm8Manager extends GenericDbManager {

    public Dm8Manager(DbOptions options) {
        super(options);
    }

    static {
        String driverClass = "dm.jdbc.driver.DmDriver";
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Could not load db driver class: " + driverClass);
        }
    }

    @Override
    public String getQueryVersionSql() {
        return "SELECT * FROM v$version where rownum <= 1";
    }

    @Override
    public String getQuerySchemaListSql() {
        return "SELECT OBJECT_NAME FROM all_objects where object_type = 'SCH' AND OWNER NOT LIKE 'SYS%'";
    }

    @Override
    public boolean alterFieldDesc(
        String dbName, String tableName, String fieldName, String fieldDes, boolean isPartition)
        throws SQLException {
        throw new SQLException("暂未实现");
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName) throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type = 'TABLE' and o.owner not like 'SYS%' and o.owner = ? ";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableSizeDomain> getTableSizeList(String dbName, String schema) throws SQLException {

        String sql = "SELECT  "
            + "    owner AS table_schema, "
            + "    segment_name AS table_name, "
            + "    blocks * 8192 AS size "
            + " FROM  dba_segments"
            + " WHERE  segment_type = 'TABLE' and owner= ? ";
        List<TableSizeDomain> list = new ArrayList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql,schema);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                TableSizeDomain domain = new TableSizeDomain();
                final String tableName = rs.getString("table_name");
                final long size = rs.getLong("size");
                final String tableSchema = rs.getString("table_schema");
                domain.setTableSize(size);
                domain.setTableName(tableName);
                domain.setSchema(tableSchema);
                list.add(domain);
            }
            return list;
        }
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName, String schema) throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type = 'TABLE' and o.owner = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, schema);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    private static TableInfoDomain getTableDomain(String dbName, ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        Set<String> set = new HashSet<>();
        for (int index = 1; index <= meta.getColumnCount(); index++) {
            set.add(meta.getColumnLabel(index));
        }

        final Date lastDdlTime = rs.getDate("last_ddl_time");

        TableInfoDomain entity = new TableInfoDomain();
        entity.setDbName(dbName);
        entity.setSchema(rs.getString("owner"));
        //    entity.setTableName(rs.getString("object_name"));
        entity.setTableName(rs.getString("object_name"));
        entity.setTblId(rs.getLong("object_id"));
        entity.setLastDdlTime(lastDdlTime == null ? null : lastDdlTime.getTime());
        entity.setTableComment(rs.getString("comments"));

        final String objectType = rs.getString("object_type");

        TableInfoDomain.TblType tblType = null;
        switch (objectType) {
            case "TABLE":
                tblType = TableInfoDomain.TblType.MANAGED_TABLE;
                break;
            case "VIEW":
                tblType = TableInfoDomain.TblType.VIEW;
                break;
            default:
                break;
        }
        entity.setTblType(tblType);

        return entity;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName) throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type ='VIEW' and o.owner not like 'SYS%'";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName, String schema) throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type ='VIEW' and o.owner = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, schema);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public boolean hasSchema() {
        return false;
    }


    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName) throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type in ('TABLE','VIEW') and o.owner not like 'SYS%' and o.owner = ? ";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName, String schema)
        throws SQLException {
        if (StringUtils.isBlank(schema)) {
            return getTableAndViewList(dbName);
        }
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type in ('TABLE','VIEW') and o.owner = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, schema);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String tableName)
        throws SQLException {
        if (StringUtils.isNotBlank(dbOptions.getSchema())) {
            return getTableInfoByTableName(dbName, dbOptions.getSchema(), tableName);
        }

        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type in ('TABLE','VIEW') and o.owner not like 'SYS%' and o.owner = ? and o.object_name = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName, tableName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String schema, String tableName)
        throws SQLException {
        if (StringUtils.isBlank(schema)) {
            return getTableInfoByTableName(dbName, tableName);
        }
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type in ('TABLE','VIEW') and o.owner = ? and o.object_name = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, dbName, tableName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    protected String getPreviewDataSql(String tableName) {
        String sql = "SELECT * FROM " + tableName + " WHERE ROWNUM <= 10";
        return sql;
    }

    @Override
    public PreviewDataDomain previewData(String schema, String tableName) throws SQLException {
        return previewData(escapeTableName(schema) + "." + escapeTableName(tableName));
    }

    public static void main(String[] args) throws SQLException {
        DbOptions options = new DbOptions();
        options.setUrl("jdbc:dm://121.37.186.235:5236/ZIGUI");
        options.setUsername("ZIGUI");
        options.setPassword("Cp19U_qfo_3");

        Dm8Manager manager = new Dm8Manager(options);
        final String dbVersion = manager.getDbVersion();
        System.out.println(dbVersion);

        final List<String> schemaList = manager.getSchemaList();
        schemaList.forEach(System.out::println);

        final List<TableInfoDomain> tableInfoDomainList = manager.getTableAndViewList("ZIGUI");
        tableInfoDomainList.forEach(System.out::println);

        final List<FieldInfoDomain> fieldList = manager.getFieldList("ZIGUI", "T1");
        fieldList.forEach(System.out::println);

        final PreviewDataDomain previewDataDomain = manager.previewData("T1");
        System.out.println(previewDataDomain);
    }
}
